﻿Imports CasaShowDados.DataAccess

Public Class AcessoBanco

    Public Shared StringConexao As String = DtStrConn.GetStrConn("CASASHOW", "localhost")

#Region "Usuário"

    ''' <summary>
    ''' Função para consultar usuários
    ''' </summary>
    ''' <returns>Usuário</returns>
    ''' <remarks>Willian - 14/11/2013</remarks>
    Public Shared Function ConsultarUsuario() As DataSet
        Return ConsultarUsuario(Nothing)
    End Function

    ''' <summary>
    ''' Função para consultar usuários
    ''' </summary>
    ''' <param name="Codigo">Código do usuário a ser retornado</param>
    ''' <returns>Usuário</returns>
    ''' <remarks>Willian - 11/11/2013</remarks>
    Public Shared Function ConsultarUsuario(ByVal Codigo As Integer) As DataSet

        Try

            Dim ParametroSQL(0) As SqlClient.SqlParameter

            ParametroSQL(0) = New SqlClient.SqlParameter("@ID_USUARIO", SqlDbType.Int)
            ParametroSQL(0).Value = IIf(IsNothing(Codigo) Or Codigo = 0, DBNull.Value, Codigo)

            Return SqlHelper.ExecuteDataset(StringConexao, CommandType.StoredProcedure, "SP02_CONSULTA_USUARIO", ParametroSQL)

        Catch ex As Exception

            Throw New Exception(ex.Message, ex)

        End Try

    End Function

    ''' <summary>
    ''' Função para cadastrar usuários
    ''' </summary>
    ''' <param name="ListaUsuario">Lista de usuários a serem cadastrados</param>
    ''' <remarks>Willian - 11/11/2013</remarks>
    Public Shared Sub CadastrarUsuario(ByVal ListaUsuario As List(Of Usuario))

        Try

            Dim ParametroSQL(0) As SqlClient.SqlParameter

            ParametroSQL(0) = New SqlClient.SqlParameter("@XML", SqlDbType.VarChar)
            ParametroSQL(0).Value = Gerais.MontaXml(Of Usuario)(ListaUsuario, "USUARIO")

            Dim a As String = ""
            a = ""
            Call SqlHelper.ExecuteNonQuery(StringConexao, CommandType.StoredProcedure, "SP01_CADASTRO_USUARIO", ParametroSQL)
            a = ""
        Catch ex As Exception

            Throw New Exception(ex.Message, ex)

        End Try

    End Sub

#End Region

#Region "Artista"

    ''' <summary>
    ''' Função para consultar artistas
    ''' </summary>
    ''' <returns>Artista</returns>
    ''' <remarks>Willian - 14/11/2013</remarks>
    Public Shared Function ConsultarArtista() As DataSet
        Return ConsultarArtista(Nothing)
    End Function

    ''' <summary>
    ''' Função para consultar artistas
    ''' </summary>
    ''' <param name="Codigo">Código do artista a ser retornado</param>
    ''' <returns>Artista</returns>
    ''' <remarks>Willian - 14/11/2013</remarks>
    Public Shared Function ConsultarArtista(ByVal Codigo As Integer) As DataSet

        Try

            Dim ParametroSQL(0) As SqlClient.SqlParameter

            ParametroSQL(0) = New SqlClient.SqlParameter("@ID_ARTISTA", SqlDbType.Int)
            ParametroSQL(0).Value = IIf(IsNothing(Codigo) Or Codigo = 0, DBNull.Value, Codigo)

            Return SqlHelper.ExecuteDataset(StringConexao, CommandType.StoredProcedure, "SP04_CONSULTA_ARTISTA", ParametroSQL)

        Catch ex As Exception

            Throw New Exception(ex.Message, ex)

        End Try

    End Function

    ''' <summary>
    ''' Função para cadastrar artistas
    ''' </summary>
    ''' <param name="ListaArtista">Lista de artistas a serem cadastrados</param>
    ''' <remarks>Willian - 14/11/2013</remarks>
    Public Shared Sub CadastrarArtista(ByVal ListaArtista As List(Of Artista))

        Try

            Dim ParametroSQL(0) As SqlClient.SqlParameter

            ParametroSQL(0) = New SqlClient.SqlParameter("@XML", SqlDbType.VarChar)
            ParametroSQL(0).Value = Gerais.MontaXml(Of Artista)(ListaArtista, "ARTISTA")

            Dim a As String = ""
            a = ""
            Call SqlHelper.ExecuteNonQuery(StringConexao, CommandType.StoredProcedure, "SP03_CADASTRO_ARTISTA", ParametroSQL)
            a = ""
        Catch ex As Exception

            Throw New Exception(ex.Message, ex)

        End Try

    End Sub

#End Region

#Region "Show"

    Public Shared Sub CadastrarShow(ByVal ListaShow As List(Of Show))

        Try

            Dim ParametroSQL(0) As SqlClient.SqlParameter

            ParametroSQL(0) = New SqlClient.SqlParameter("@XML", SqlDbType.VarChar)
            ParametroSQL(0).Value = Gerais.MontaXml(Of Show)(ListaShow, "SHOW")

            Call SqlHelper.ExecuteNonQuery(StringConexao, CommandType.StoredProcedure, "SP05_CADASTRO_SHOW", ParametroSQL)

        Catch ex As Exception

            Throw New Exception(ex.Message, ex)

        End Try

    End Sub

    Public Shared Function ConsultarShow() As DataSet
        Return ConsultarShow(Nothing)
    End Function

    Public Shared Function ConsultarShow(ByVal Codigo As Integer) As DataSet

        Try

            Dim ParametroSQL(0) As SqlClient.SqlParameter

            ParametroSQL(0) = New SqlClient.SqlParameter("@ID_SHOW", SqlDbType.Int)
            ParametroSQL(0).Value = IIf(IsNothing(Codigo) Or Codigo = 0, DBNull.Value, Codigo)

            Return SqlHelper.ExecuteDataset(StringConexao, CommandType.StoredProcedure, "SP06_CONSULTA_SHOW", ParametroSQL)

        Catch ex As Exception

            Throw New Exception(ex.Message, ex)

        End Try

    End Function

#End Region

#Region "Ingresso"

    Public Shared Sub CadastrarIngresso(ByVal ListaIngresso As List(Of Ingresso))

        Try

            Dim ParametroSQL(0) As SqlClient.SqlParameter

            ParametroSQL(0) = New SqlClient.SqlParameter("@XML", SqlDbType.VarChar)
            ParametroSQL(0).Value = Gerais.MontaXml(Of Ingresso)(ListaIngresso, "INGRESSO")

            Call SqlHelper.ExecuteNonQuery(StringConexao, CommandType.StoredProcedure, "SP07_CADASTRO_INGRESSO", ParametroSQL)

        Catch ex As Exception

            Throw New Exception(ex.Message, ex)

        End Try

    End Sub

    Public Shared Function ConsultarIngresso() As DataSet
        Return ConsultarIngresso(Nothing)
    End Function

    Public Shared Function ConsultarIngresso(ByVal Codigo As Integer) As DataSet

        Try

            Dim ParametroSQL(0) As SqlClient.SqlParameter

            ParametroSQL(0) = New SqlClient.SqlParameter("@ID_INGRESSO", SqlDbType.Int)
            ParametroSQL(0).Value = IIf(IsNothing(Codigo) Or Codigo = 0, DBNull.Value, Codigo)

            Return SqlHelper.ExecuteDataset(StringConexao, CommandType.StoredProcedure, "SP08_CONSULTA_INGRESSO", ParametroSQL)

        Catch ex As Exception

            Throw New Exception(ex.Message, ex)

        End Try

    End Function

#End Region

#Region "Compra"

    Public Shared Sub EfetuarCompra(ByVal CodigoUsuario As Integer, ByVal CodigoIngresso As Integer, ByVal Quantidade As Integer)

        Dim ParametroSQL(2) As SqlClient.SqlParameter

        ParametroSQL(0) = New SqlClient.SqlParameter("@ID_USUARIO", SqlDbType.Int)
        ParametroSQL(0).Value = CodigoUsuario

        ParametroSQL(1) = New SqlClient.SqlParameter("@ID_INGRESSO", SqlDbType.Int)
        ParametroSQL(1).Value = CodigoIngresso

        ParametroSQL(2) = New SqlClient.SqlParameter("@QT_INGRESSO", SqlDbType.Int)
        ParametroSQL(2).Value = Quantidade

        Call SqlHelper.ExecuteNonQuery(StringConexao, CommandType.StoredProcedure, "SP09_EFETUA_COMPRA", ParametroSQL)


    End Sub

    Public Shared Function ConsultarCompra() As DataSet
        Return ConsultarCompra(Nothing)
    End Function

    Public Shared Function ConsultarCompra(ByVal Codigo As Integer) As DataSet

        Try

            Dim ParametroSQL(0) As SqlClient.SqlParameter

            ParametroSQL(0) = New SqlClient.SqlParameter("@ID_COMPRA", SqlDbType.Int)
            ParametroSQL(0).Value = IIf(IsNothing(Codigo) Or Codigo = 0, DBNull.Value, Codigo)

            Return SqlHelper.ExecuteDataset(StringConexao, CommandType.StoredProcedure, "SP10_CONSULTA_INGRESSO", ParametroSQL)

        Catch ex As Exception

            Throw New Exception(ex.Message, ex)

        End Try

    End Function

#End Region




End Class
